{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exporting Simulation Results\n",
    "*Exporting Scenario Data For Further Analysis In High-End Business Intelligence Tools*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Jupyter Notebooks are a perfect environment to create System Dynamics and Agent-based Models and analyse them in-depth – at least if you are a data scientist or computational modeler.\n",
    "\n",
    "But what if you want to present your results to people who are not quite so tech savvy or don't have all the necessary tools installed?\n",
    "\n",
    "We face such situations quite often with our clients and at one point we asked ourselves:\n",
    "\n",
    "* Why not use a high-end business intelligence tool such as Microsofts Power BI Desktop to create a polished dasboards?\n",
    "* Why not share the reports using the Power BI service? After all, Power BI was created to create such data intelligence apps!\n",
    "* Why not use Jupyter notebooks to create sophisticated simulation models (which is what the Jupyter+Python Ecosystem is good at) and then use Power BI for the fancy UI (which is what Power BI is good at)\n",
    "\n",
    "To achieve this, all we really need BPTK-Py to do is to export the data generated by the simulation for those scenarios that are relevant for the report.\n",
    "\n",
    "We can then import that data into Power BI and build the report using Power BI's WYSIWIG tools.\n",
    "\n",
    "To achieve this, we've added a new method called `export_scenarios` to BPTK-Py which writes the data for a set of scenarios and interactive settings to an Excel file.\n",
    "\n",
    "Here is what the method looks like for the customer acquisition model, which is one of the models we've provided with the tutorial:\n",
    "\n",
    "```python\n",
    "bptk.export_scenarios(\n",
    "    scenario_manager=\"smCustomerAcquisition\",\n",
    "    equations=[\"customers\",\"profit\"],\n",
    "    filename='/path/to/exported/data/customer_aquisition.xlsx',\n",
    "    interactive_scenario=\"interactiveScenario\",\n",
    "    interactive_equations=[\"customers\",\"profit\"],\n",
    "    interactive_settings= {\n",
    "        \"advertisingSuccessPct\":(0,0.2,0.01),\n",
    "        \"referralFreeMonths\":(0,40,10),\n",
    "        \"referralProgramAdoptionPct\":(0,12,1),\n",
    "        \"referrals\":(0,12,1)\n",
    "    }\n",
    ") \n",
    "```\n",
    "\n",
    "And here is a Power BI report we've created from the data, you can access it [directly](https://app.powerbi.com/view?r=eyJrIjoiNDFlMTA2ZGItYmFkOC00Mjg3LTljODYtYzRkYzY2YmFmM2E4IiwidCI6ImZjNWRmZDc1LTQ1NzktNGQ3MC05YTE3LTk0MmQ0OWMwMTc2ZiIsImMiOjl9) on PowerBI:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"933\"\n",
       "            height=\"700\"\n",
       "            src=\"https://app.powerbi.com/view?r=eyJrIjoiNDFlMTA2ZGItYmFkOC00Mjg3LTljODYtYzRkYzY2YmFmM2E4IiwidCI6ImZjNWRmZDc1LTQ1NzktNGQ3MC05YTE3LTk0MmQ0OWMwMTc2ZiIsImMiOjl9\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x10ee0d400>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.display import IFrame       \n",
    "IFrame('https://app.powerbi.com/view?r=eyJrIjoiNDFlMTA2ZGItYmFkOC00Mjg3LTljODYtYzRkYzY2YmFmM2E4IiwidCI6ImZjNWRmZDc1LTQ1NzktNGQ3MC05YTE3LTk0MmQ0OWMwMTc2ZiIsImMiOjl9', width=933, height=700)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A Closer Look At BPTK-Py's Export Function"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This section takes a closer look at how the export function is implemented, just in case you would like to add some features or export the data in some other format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Load the BPTK Package\n",
    "from BPTK_Py.bptk import bptk \n",
    "bptk = bptk()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The first thing to remember is how scenario data is stored in a `pandas` dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=bptk.plot_scenarios(\n",
    "    scenario_managers=[\"smCustomerAcquisition\"],\n",
    "    scenarios=[\"referSomeonePlease\"],\n",
    "    equations=[\"customers\",\"profit\"],\n",
    "    return_df=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customers</th>\n",
       "      <th>profit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>t</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>800.000000</td>\n",
       "      <td>-1.020000e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.0</th>\n",
       "      <td>2319.797333</td>\n",
       "      <td>-1.043199e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3.0</th>\n",
       "      <td>5206.498408</td>\n",
       "      <td>-1.072470e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4.0</th>\n",
       "      <td>10687.586632</td>\n",
       "      <td>-1.113255e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5.0</th>\n",
       "      <td>21087.855912</td>\n",
       "      <td>-1.175834e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6.0</th>\n",
       "      <td>40797.409870</td>\n",
       "      <td>-1.279519e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7.0</th>\n",
       "      <td>78059.974800</td>\n",
       "      <td>-1.460212e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8.0</th>\n",
       "      <td>148189.540173</td>\n",
       "      <td>-1.783312e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9.0</th>\n",
       "      <td>279046.346751</td>\n",
       "      <td>-2.363130e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10.0</th>\n",
       "      <td>519270.823101</td>\n",
       "      <td>-3.382515e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          customers        profit\n",
       "t                                \n",
       "1.0      800.000000 -1.020000e+06\n",
       "2.0     2319.797333 -1.043199e+06\n",
       "3.0     5206.498408 -1.072470e+06\n",
       "4.0    10687.586632 -1.113255e+06\n",
       "5.0    21087.855912 -1.175834e+06\n",
       "6.0    40797.409870 -1.279519e+06\n",
       "7.0    78059.974800 -1.460212e+06\n",
       "8.0   148189.540173 -1.783312e+06\n",
       "9.0   279046.346751 -2.363130e+06\n",
       "10.0  519270.823101 -3.382515e+06"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[1:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that each indicator (aka equation, `customers` and `profit` in this example) has its own column and that the time dimension forms the index of the dataframe. Also notice that the name of the scenario `referSomeonePlease` is not stored in the dataframe itself.\n",
    "\n",
    "Now when it comes to displaying the data in an interactive report (like the one above) we would like to be able to switch between scenarios. So one thing we need to do is to add a column containing a name of the scenario to the dataframe. We also would like one large dataframe containing the data from all the scenarios."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get all the scenarios from the smCustomerAquisition scenario manager\n",
    "scenario_manager=\"smCustomerAcquisition\"\n",
    "scenarios = bptk.get_scenario_names([scenario_manager])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['base',\n",
       " 'serviceFlop',\n",
       " 'rethinkAdvertising',\n",
       " 'referSomeonePlease',\n",
       " 'hereWeGo',\n",
       " 'boomButBust']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scenarios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# List of equations we want to generate data for\n",
    "equations=[\"customers\",\"profit\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a new dataframe with a column for each equation, indexed by time and scenario\n",
    "scenario_dfs = []\n",
    "for scenario in scenarios:\n",
    "    # first create a dataframe for each scenario\n",
    "    df = bptk.plot_scenarios(\n",
    "        scenario_managers=[scenario_manager],\n",
    "        scenarios=[scenario],\n",
    "        equations=equations,\n",
    "        return_df=True)\n",
    "    # add a colum which will contain the name of the scenario\n",
    "    df[\"scenario\"] = [scenario] * len(df.index)\n",
    "    # create a new column which will contain the time step (which won't be a unique index anymore, as we are concatenating many scenarios)\n",
    "    df[\"time\"] = df.index\n",
    "    scenario_dfs += [df]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[         customers        profit scenario  time\n",
       " t                                              \n",
       " 0.0       0.000000 -1.000000e+06     base   0.0\n",
       " 1.0     800.000000 -1.010000e+06     base   1.0\n",
       " 2.0    1599.893333 -1.016000e+06     base   2.0\n",
       " 3.0    2399.680014 -1.018001e+06     base   3.0\n",
       " 4.0    3199.360057 -1.016002e+06     base   4.0\n",
       " ...            ...           ...      ...   ...\n",
       " 56.0  44636.126878  4.585242e+06     base  56.0\n",
       " 57.0  45430.175394  4.798423e+06     base  57.0\n",
       " 58.0  46224.118038  5.015574e+06     base  58.0\n",
       " 59.0  47017.954822  5.236694e+06     base  59.0\n",
       " 60.0  47811.685761  5.461784e+06     base  60.0\n",
       " \n",
       " [61 rows x 4 columns],\n",
       "          customers        profit     scenario  time\n",
       " t                                                  \n",
       " 0.0   0.000000e+00 -1.000000e+06  serviceFlop   0.0\n",
       " 1.0   8.000000e+02 -1.020000e+06  serviceFlop   1.0\n",
       " 2.0   2.319797e+03 -1.043199e+06  serviceFlop   2.0\n",
       " 3.0   5.206498e+03 -1.072470e+06  serviceFlop   3.0\n",
       " 4.0   1.068759e+04 -1.113255e+06  serviceFlop   4.0\n",
       " ...            ...           ...          ...   ...\n",
       " 56.0  6.000000e+06  1.216192e+09  serviceFlop  56.0\n",
       " 57.0  6.000000e+06  1.246172e+09  serviceFlop  57.0\n",
       " 58.0  6.000000e+06  1.276152e+09  serviceFlop  58.0\n",
       " 59.0  6.000000e+06  1.306132e+09  serviceFlop  59.0\n",
       " 60.0  6.000000e+06  1.336112e+09  serviceFlop  60.0\n",
       " \n",
       " [61 rows x 4 columns]]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scenario_dfs[0:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# concatenate the data into one table using pandas concat function\n",
    "scenarios_tab = pd.concat(scenario_dfs, ignore_index=True, sort=False)\n",
    "scenarios_tab.index.name=\"id\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customers</th>\n",
       "      <th>profit</th>\n",
       "      <th>scenario</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>-1.000000e+06</td>\n",
       "      <td>base</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8.000000e+02</td>\n",
       "      <td>-1.010000e+06</td>\n",
       "      <td>base</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.599893e+03</td>\n",
       "      <td>-1.016000e+06</td>\n",
       "      <td>base</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2.399680e+03</td>\n",
       "      <td>-1.018001e+06</td>\n",
       "      <td>base</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.199360e+03</td>\n",
       "      <td>-1.016002e+06</td>\n",
       "      <td>base</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>5.999520e+06</td>\n",
       "      <td>6.289686e+08</td>\n",
       "      <td>boomButBust</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>5.999664e+06</td>\n",
       "      <td>6.589419e+08</td>\n",
       "      <td>boomButBust</td>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>363</th>\n",
       "      <td>5.999765e+06</td>\n",
       "      <td>6.889172e+08</td>\n",
       "      <td>boomButBust</td>\n",
       "      <td>58.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>364</th>\n",
       "      <td>5.999835e+06</td>\n",
       "      <td>7.188939e+08</td>\n",
       "      <td>boomButBust</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>365</th>\n",
       "      <td>5.999885e+06</td>\n",
       "      <td>7.488716e+08</td>\n",
       "      <td>boomButBust</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>366 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        customers        profit     scenario  time\n",
       "id                                                \n",
       "0    0.000000e+00 -1.000000e+06         base   0.0\n",
       "1    8.000000e+02 -1.010000e+06         base   1.0\n",
       "2    1.599893e+03 -1.016000e+06         base   2.0\n",
       "3    2.399680e+03 -1.018001e+06         base   3.0\n",
       "4    3.199360e+03 -1.016002e+06         base   4.0\n",
       "..            ...           ...          ...   ...\n",
       "361  5.999520e+06  6.289686e+08  boomButBust  56.0\n",
       "362  5.999664e+06  6.589419e+08  boomButBust  57.0\n",
       "363  5.999765e+06  6.889172e+08  boomButBust  58.0\n",
       "364  5.999835e+06  7.188939e+08  boomButBust  59.0\n",
       "365  5.999885e+06  7.488716e+08  boomButBust  60.0\n",
       "\n",
       "[366 rows x 4 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scenarios_tab"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now have all the data for all the scenarios in one large dataframe. Each row is indexed by the scenario it belongs to. The timestamp is only unique within a given scenario."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generating The Data For Scenario Comparison\n",
    "\n",
    "The data we have generated so far is a table with a column for each indicator, indexed by scenario.\n",
    "\n",
    "This is fine if you want to look at data scenario by scenario or plot two different indicators for the same scenario.\n",
    "\n",
    "But what if you want to compare the same indicator for different scenarios?\n",
    "\n",
    "In such a case, your data needs to be structured a little differently - essential we then want a table with a column for each scenario, indexed by the indicator.\n",
    "\n",
    "To achieve this, we need to loop through the scenarios again:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a new dataframe with a column for each scenario, indexed by time and indicator\n",
    "indicator_dfs = []\n",
    "for scenario_no, scenario in enumerate(scenarios):\n",
    "    \n",
    "    scenario_dfs=[]\n",
    "    # loop through the equations\n",
    "    for equation in equations:\n",
    "        # add a column which will contain the name of the indicator\n",
    "        df = bptk.plot_scenarios(\n",
    "            scenario_managers=[scenario_manager],\n",
    "            scenarios=[scenario],\n",
    "            equations=[equation],\n",
    "            return_df=True)\n",
    "        df.rename(columns={equation:scenario},inplace=True)\n",
    "        if scenario_no is len(scenarios)-1:\n",
    "            df[\"indicator\"] = [equation] * len(df.index)\n",
    "            df[\"time\"] = df.index\n",
    "        scenario_dfs +=[df]\n",
    "    \n",
    "    # conacate the indicators for the scenario (i.e. along axis 0)\n",
    "    indicators_scenario_tab = pd.concat(scenario_dfs, axis=0,ignore_index=True,sort=False)\n",
    "    \n",
    "    # create a new column which will contain the time step (which won't be a unique index anymore, as we are concatenating many scenarios)\n",
    "    \n",
    "    indicator_dfs += [indicators_scenario_tab]\n",
    "\n",
    "#concatenate all the scenario columns (i.e. along axis 1)\n",
    "indicators_tab=pd.concat(indicator_dfs,axis=1,sort=False)\n",
    "indicators_tab.index.name=\"id\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>base</th>\n",
       "      <th>serviceFlop</th>\n",
       "      <th>rethinkAdvertising</th>\n",
       "      <th>referSomeonePlease</th>\n",
       "      <th>hereWeGo</th>\n",
       "      <th>boomButBust</th>\n",
       "      <th>indicator</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>customers</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>800.000000</td>\n",
       "      <td>800.000000</td>\n",
       "      <td>800.000000</td>\n",
       "      <td>800.000000</td>\n",
       "      <td>800.000000</td>\n",
       "      <td>800.000000</td>\n",
       "      <td>customers</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1599.893333</td>\n",
       "      <td>2319.797333</td>\n",
       "      <td>2319.797333</td>\n",
       "      <td>2319.797333</td>\n",
       "      <td>2319.797333</td>\n",
       "      <td>1839.861333</td>\n",
       "      <td>customers</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2399.680014</td>\n",
       "      <td>5206.498408</td>\n",
       "      <td>5206.498408</td>\n",
       "      <td>5206.498408</td>\n",
       "      <td>5206.498408</td>\n",
       "      <td>3191.405164</td>\n",
       "      <td>customers</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3199.360057</td>\n",
       "      <td>10687.586632</td>\n",
       "      <td>10687.586632</td>\n",
       "      <td>10687.586632</td>\n",
       "      <td>10687.586632</td>\n",
       "      <td>4947.891939</td>\n",
       "      <td>customers</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3998.933476</td>\n",
       "      <td>21087.855912</td>\n",
       "      <td>21087.855912</td>\n",
       "      <td>21087.855912</td>\n",
       "      <td>21087.855912</td>\n",
       "      <td>7230.375720</td>\n",
       "      <td>customers</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4798.400284</td>\n",
       "      <td>40797.409870</td>\n",
       "      <td>40797.409870</td>\n",
       "      <td>40797.409870</td>\n",
       "      <td>40797.409870</td>\n",
       "      <td>10195.910470</td>\n",
       "      <td>customers</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5597.760498</td>\n",
       "      <td>78059.974800</td>\n",
       "      <td>78059.974800</td>\n",
       "      <td>78059.974800</td>\n",
       "      <td>78059.974800</td>\n",
       "      <td>14048.126326</td>\n",
       "      <td>customers</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>6397.014130</td>\n",
       "      <td>148189.540173</td>\n",
       "      <td>148189.540173</td>\n",
       "      <td>148189.540173</td>\n",
       "      <td>148189.540173</td>\n",
       "      <td>19050.823648</td>\n",
       "      <td>customers</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7196.161194</td>\n",
       "      <td>279046.346751</td>\n",
       "      <td>279046.346751</td>\n",
       "      <td>279046.346751</td>\n",
       "      <td>279046.346751</td>\n",
       "      <td>25545.383938</td>\n",
       "      <td>customers</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           base    serviceFlop  rethinkAdvertising  referSomeonePlease  \\\n",
       "id                                                                       \n",
       "0      0.000000       0.000000            0.000000            0.000000   \n",
       "1    800.000000     800.000000          800.000000          800.000000   \n",
       "2   1599.893333    2319.797333         2319.797333         2319.797333   \n",
       "3   2399.680014    5206.498408         5206.498408         5206.498408   \n",
       "4   3199.360057   10687.586632        10687.586632        10687.586632   \n",
       "5   3998.933476   21087.855912        21087.855912        21087.855912   \n",
       "6   4798.400284   40797.409870        40797.409870        40797.409870   \n",
       "7   5597.760498   78059.974800        78059.974800        78059.974800   \n",
       "8   6397.014130  148189.540173       148189.540173       148189.540173   \n",
       "9   7196.161194  279046.346751       279046.346751       279046.346751   \n",
       "\n",
       "         hereWeGo   boomButBust  indicator  time  \n",
       "id                                                \n",
       "0        0.000000      0.000000  customers   0.0  \n",
       "1      800.000000    800.000000  customers   1.0  \n",
       "2     2319.797333   1839.861333  customers   2.0  \n",
       "3     5206.498408   3191.405164  customers   3.0  \n",
       "4    10687.586632   4947.891939  customers   4.0  \n",
       "5    21087.855912   7230.375720  customers   5.0  \n",
       "6    40797.409870  10195.910470  customers   6.0  \n",
       "7    78059.974800  14048.126326  customers   7.0  \n",
       "8   148189.540173  19050.823648  customers   8.0  \n",
       "9   279046.346751  25545.383938  customers   9.0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "indicators_tab[0:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generating The Data For Interactive Dashboards"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In most cases creating an interactive report that just compares predefined scenarios is quite enough. But sometimes you would like to add a little dashboard to allow users to test different settings themselves, like the \"Forecast\" page in the example above.\n",
    "\n",
    "The easiest way to achive this in Power BI is to use so called \"What If\" parameters to select a scenario from a set of pre-computed scenarios. We need to pre-compute them because currently Power BI doesn't allow you to query data live with different parameters.\n",
    "\n",
    "In most cases there will be thousands of \"interactive\" scenarios you need to pre-compute, so it is not feasible to enumerate them as a list. Instead the idea is to start with a base \"interactive\" scenario and then vary a set of parameters within a given range, much like in a Monte Carlo simulation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "*** smCustomerAcquisition ***\n",
      "\t base\n",
      "\t serviceFlop\n",
      "\t rethinkAdvertising\n",
      "\t referSomeonePlease\n",
      "\t hereWeGo\n",
      "\t boomButBust\n"
     ]
    }
   ],
   "source": [
    "# these are the scenarios initially defined\n",
    "bptk.list_scenarios([\"smCustomerAcquisition\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# a new scenario which will act as a base for generating the data needed for interactive dashboards\n",
    "bptk.register_scenarios(scenario_manager=\"smCustomerAcquisition\",scenarios={\n",
    "                          \"interactiveScenario\":{\n",
    "                              \"constants\":{\n",
    "                                 \"referrals\":0,\n",
    "                                  \"advertisingSuccessPct\":0.1,\n",
    "                                  \"referralFreeMonths\":3,\n",
    "                                  \"referralProgamAdoptionPct\":10\n",
    "                                }\n",
    "                          }\n",
    "}\n",
    "                    \n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another call to `list_scenarios` shows that the interactive scenario has now been added:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "*** smCustomerAcquisition ***\n",
      "\t base\n",
      "\t serviceFlop\n",
      "\t rethinkAdvertising\n",
      "\t referSomeonePlease\n",
      "\t hereWeGo\n",
      "\t boomButBust\n",
      "\t interactiveScenario\n"
     ]
    }
   ],
   "source": [
    "bptk.list_scenarios([\"smCustomerAcquisition\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have a scenario, we need to define the \"What if\" parameters:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for each parameter we define the range of settings - e.g. referrals ranges from 0 to twelve, with a step of one: 1,2, 3... 12.\n",
    "# advertisingSuccessPct ranges from 0 to 0.2, with a steop of 0.01: 0, 0.01, 0.02,....\n",
    "interactive_settings= {\n",
    "    \"advertisingSuccessPct\":(0,0.2,0.01),\n",
    "    \"referralFreeMonths\":(0,40,10),\n",
    "    \"referralProgramAdoptionPct\":(0,12,1),\n",
    "    \"referrals\":(0,12,1)\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we need to pre-compute all possible combinations, which is quite a few for the ranges defined above:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11520"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np # will use the arange function to create ranges with fractional steps\n",
    "len(np.arange(0,0.2,0.01))*len(np.arange(0,40,10))*len(np.arange(0,12,1))*len(np.arange(0,12,1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "import itertools # will use the product function to iterate through all possible combinations\n",
    "\n",
    "# generate all combinations of the settings\n",
    "dimensions = [interactive_settings[key] for key in interactive_settings]\n",
    "#now generate all possible settings\n",
    "settings = list(itertools.product(*tuple(itertools.starmap(np.arange, dimensions))))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The last line of code uses some advanved functional programming to generates all possible combinations of the interactive parameters ... let's take a look at what it does using just two interactive parameters:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 4), (1, 5), (1, 6), (2, 4), (2, 5), (2, 6), (3, 4), (3, 5), (3, 6)]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(itertools.product(*tuple(itertools.starmap(np.arange,[(1,4,1),(4,7,1)]))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11520"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(settings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# IMPORTANT: this code takes quite some time to run (ca. 4min on my machine)\n",
    "# a variable to store the dataframes\n",
    "interactive_dfs = []\n",
    "interactive_scenario=\"interactiveScenario\"\n",
    "interactive_equations=[\"customers\",\"profit\"]\n",
    "scenario = bptk.get_scenario(scenario_manager, interactive_scenario)\n",
    "# now apply the settings to the scenario\n",
    "for setting in settings:\n",
    "    for setting_index, key in enumerate(interactive_settings):\n",
    "        scenario.set_property_value(key, setting[setting_index])\n",
    "    bptk.reset_scenario_cache(\n",
    "            scenario_manager=scenario_manager,\n",
    "            scenario=interactive_scenario\n",
    "    )\n",
    "    df = bptk.plot_scenarios(\n",
    "        scenario_managers=[scenario_manager],\n",
    "        scenarios=[interactive_scenario],\n",
    "        equations=interactive_equations,\n",
    "        return_df=True\n",
    "    )\n",
    "    # add columns for the settings\n",
    "    for setting_index, key in enumerate(interactive_settings):\n",
    "        df[key] = [setting[setting_index]] * len(df.index)\n",
    "    # explicitly set a time column\n",
    "    df[\"time\"] = df.index\n",
    "    interactive_dfs += [df]\n",
    "# concatenate the interactive scenarios\n",
    "interactive_tab = pd.concat(interactive_dfs, ignore_index=True, sort=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "702720"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(interactive_tab)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "702720"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "11520*61 # number of scenarios * number of timesteps"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing The Dataframes To An Excel File"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have the dataframes containg the data, we can write them to an Excel file using Pandas `ExcelWriter` function. Note that this relies on the `xlsxwriter` package:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlsxwriter\n",
    "filename=\"./data/customer_acquisition.xlsx\"\n",
    "with pd.ExcelWriter(filename) as writer:\n",
    "    scenarios_tab.to_excel(writer, sheet_name=\"scenarios\")\n",
    "    indicators_tab.to_excel(writer, sheet_name=\"indicators\")\n",
    "    interactive_tab.to_excel(writer, sheet_name=\"interactive\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calling The Export Function Directly"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is how you would call the `export_scenarios` function directly – it you don't pass a filename it returns a dictionary containing the dataframes for both the scenarios and the interactive dashboard. \n",
    "\n",
    "> Important: With the given parameters the export function generates over 11.000 interactive scenarios amounting to around 30MB of data. On my machine (a Macbook Pro with 16MB of RAM) the function takes just under three minutes to complete."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2min 47s, sys: 4.98 s, total: 2min 52s\n",
      "Wall time: 2min 52s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "## save the file in the current working directory\n",
    "import os\n",
    "filename= os.path.join(os.getcwd(),\"data\",\"customer_acquisition.xlsx\")\n",
    "\n",
    "## Load the BPTK Package\n",
    "bptk.export_scenarios(\n",
    "    scenario_manager=\"smCustomerAcquisition\",\n",
    "    equations=[\"customers\",\"profit\"],\n",
    "    filename=filename,\n",
    "    interactive_scenario=\"interactiveScenario\",\n",
    "    interactive_equations=[\"customers\",\"profit\"],\n",
    "    interactive_settings= {\n",
    "        \"advertisingSuccessPct\":(0,0.2,0.01),\n",
    "        \"referralFreeMonths\":(0,40,10),\n",
    "        \"referralProgramAdoptionPct\":(0,12,1),\n",
    "        \"referrals\":(0,12,1)\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
